/**
 * 
 */
package ipms.necfgsyn;

import ipms.appbase.ClsDataBase;
import ipms.snmp.SnmpV2;

import java.io.IOException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.snmp4j.PDU;
import org.snmp4j.smi.OID;
import org.snmp4j.smi.Variable;
import org.snmp4j.smi.VariableBinding;

/**
 * @author spdjnh 2012.12.26 实现ICfgSynRmi接口，完成同步操作
 */
public class CfgSynImpl extends UnicastRemoteObject implements ICfgSynRmi {

	private static final long serialVersionUID = 8264996210567575827L;
	private static final String sSuccess = "Success";
	private static List<Integer> lstCurNe = Collections
			.synchronizedList(new LinkedList<Integer>());
	private static boolean bAll = false; // 是否正在做全网同步
	private static Logger log = LoggerFactory.getLogger(CfgSynImpl.class);

	private static int snmpTimeOut = 1000;
	private static int snmpRetries = 0;
	private static String getCommunity = "public";
	private static String setCommunity = "private";

	private static Connection conn = null;

	@SuppressWarnings("rawtypes")
	private static HashMap rowsNeInfo; // 存放ne_info表数据

	private SnmpV2 snmpV2 = null;

	public CfgSynImpl() throws Exception {

	}

	@Override
	public void finalize() {
		if (null != conn)
			try {
				conn.close();
			} catch (SQLException ex) {
			}

	}

	public boolean iniParam() {
		boolean b = true;
		try {
			String s = AppRunTime.MyProp.getProperty("SnmpTimeout").trim();
			snmpTimeOut = Integer.parseInt(s);
			s = AppRunTime.MyProp.getProperty("SnmpRetries").trim();
			snmpRetries = Integer.parseInt(s);
			getCommunity = AppRunTime.MyProp.getProperty("GetCommunity").trim();
			setCommunity = AppRunTime.MyProp.getProperty("SetCommunity").trim();

			log.info("Initilize SNMP parameter success.");
		} catch (Exception e) {
			b = false;
			log.error("iniParam()", e);
		}

		return b;
	}

	/**
	 * 装载网元信息表
	 * 
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public boolean loadNeInfo() {
		boolean b = true;

		try {
			getConn();

			// 2013.8.19，原来装载net_info表，现改为 Physical_NE_Table
			String sql = "select id,nename,ipaddr,activestatus,type,snmpcommunityread,snmpcommunitywrite,snmpversion "
					+ "from Physical_NE_Table where trim(ipaddr)<>''";
			ArrayList<HashMap> rows = ClsDataBase.getTable(ClsDataBase
					.execQuery(conn, sql));

			// 如果获取了最新的网元信息，则清除原来内存数据
			if (rows.size() > 0)
				rowsNeInfo = new HashMap();
			for (HashMap row : rows) {
				rowsNeInfo.put(row.get("id"), row);
			}

			log.info("Load Physical_NE_Table success.");

		} catch (Exception e) {
			b = false;
			log.error("loadNetInfo()", e);
		} finally {

		}

		return b;
	}

	// 检查数据库连接是否可用
	private void getConn() throws Exception {
		if (null != conn && conn.isValid(30))
			return;

		conn = ClsDataBase.getConn();
	}

	@SuppressWarnings("rawtypes")
	private HashMap getNe(int iNeId) {

		if (rowsNeInfo.containsKey(iNeId)) {
			return (HashMap) rowsNeInfo.get(iNeId);
		} else if (loadNeInfo()) { // 如果网元没找到，重新从数据库装载一次
			if (rowsNeInfo.containsKey(iNeId)) {
				return (HashMap) rowsNeInfo.get(iNeId);
			}
		}

		log.info("No info for NeId = " + String.valueOf(iNeId));
		return null;
	}

	@Override
	public String sync() {

		String str = "";
		// 先检查是否正在做全网同步
		if (bAll) {
			return "ALL NEs are being synchronized by another client!";
		}

		bAll = true;

		// 每次同步所有网元前，先重新装载net_info表
		loadNeInfo();

		@SuppressWarnings("unchecked")
		Iterator<Integer> iter = rowsNeInfo.keySet().iterator();
		while (iter.hasNext()) {
			String s = sync(iter.next());
			if (!s.equals(sSuccess)) {
				str += s + "\r\n";
			}

		}

		if (str.isEmpty()) {
			str = sSuccess;
			log.info("All NEs have been synchronized");
		} else {
			log.info("Some NEs have been synchronized with errors");
		}

		bAll = false;

		return str;
	}

	@Override
	public String sync(int iNeId) {
		String s = sSuccess;

		// 当前网元如果已经在同步，则直接返回
		if (lstCurNe.contains(iNeId))
			return "The NE '" + String.valueOf(iNeId)
					+ "' is being synchronized by another client!";

		@SuppressWarnings("rawtypes")
		HashMap ne = getNe(iNeId);
		if (null == ne)
			return "No info for NeId = " + String.valueOf(iNeId);

		String ipaddr = (String) ne.get("ipaddr");
		String snmpcommunityread = (String) ne.get("snmpcommunityread");
		if (snmpcommunityread.isEmpty())
			snmpcommunityread = getCommunity;
		String nename = (String) ne.get("nename");
		String sneid = String.valueOf(iNeId);

		log.info("synchronizing for " + nename + "(" + ipaddr + ")");

		try {
			snmpV2 = new SnmpV2(ipaddr, snmpcommunityread, snmpRetries,
					snmpTimeOut);

			getConn();

			// 登记当前同步的网元
			lstCurNe.add(Integer.valueOf(iNeId));

			//2013.8.19,原来(sneid, nename)，现不需要nename
			cfgChassisTable(sneid);// 机框
			cfgChassisFanTable(sneid);// 风扇
			cfgChassisPowerSupplyTable(sneid);// 电源
			cfgCpmCardTable(sneid);// 主控卡槽
			cfgCpmFlashTable(sneid);// 闪存
			cfgCardTable(sneid); // 板卡槽
			cfgMDATable(sneid); // 子板卡槽
			cfgPortTable(sneid); // 端口
			cfgHwTable(sneid); // 硬件信息表

		} catch (Exception e) {
			log.error("sync(" + String.valueOf(iNeId) + ")", e);
			s = e.toString();
		} finally {
			lstCurNe.remove(Integer.valueOf(iNeId));
			try {
				snmpV2.close();
			} catch (IOException e) {
				log.error("sync(" + String.valueOf(iNeId) + ").snmpV2.close()",
						e);
			}
		}

		return s;
	}

	// 机框
	private void cfgChassisTable(String sneid) throws Exception {

		// 获取时间，格式如"2013-01-05 15:51:29"
		String sdate = String.format("%1$tY-%1$tm-%1$td %1$tH:%1$tM:%1$tS",
				new Date());
		ArrayList<String> alSql = new ArrayList<String>();
		String sql = "insert into CM_tmnxChassisTable(neId,tmnxChassisIndex,tmnxChassisName,"
				+ "tmnxChassisType,tmnxChassisBaseMacAddress,tmnxChassisRowStatus,recTime) values("
				+ sneid + ",";

		ArrayList<HashMap<String, Variable>> rows;

		// 取出tmnxChassisTypeTable
		HashMap<Integer, Variable> hmChassisType = new HashMap<Integer, Variable>();
		rows = snmpV2.getTable(MibConst.tmnxChassisTypeTable);
		for (HashMap<String, Variable> row : rows) {
			OID ins = (OID) row.get("instance");
			hmChassisType
					.put(ins.getValue()[0], row.get("tmnxChassisTypeName"));
		}

		rows = snmpV2.getTable(MibConst.tmnxChassisTable);
		for (HashMap<String, Variable> row : rows) {
			String tmnxChassisType = "";
			if (hmChassisType.containsKey(row.get("tmnxChassisType").toInt()))
				tmnxChassisType = hmChassisType.get(
						row.get("tmnxChassisType").toInt()).toString();
			else
				tmnxChassisType = "Unkown("
						+ row.get("tmnxChassisType").toString() + ")";

			alSql.add(sql
					+ row.get("instance").toString()
					+ ",'"
					+ row.get("tmnxChassisName")
					+ "','"
					+ tmnxChassisType
					+ "','"
					+ row.get("tmnxChassisBaseMacAddress").toString()
					+ "','"
					+ MibConst.RowStatus(row.get("tmnxChassisRowStatus")
							.toInt()) + "','" + sdate + "')");
		}

		// if(alSql.size()>0)
		// alSql.add("insert into data_state(neName,tableName,lastTime) values('"
		// + nename + "','CM_tmnxChassisTable','" + sdate +"')");

		//ClsDataBase.execSql(conn, alSql);

		if (alSql.size() > 0) {
			alSql.add("delete from data_state where neId="
				+ sneid + " and tableName='CM_tmnxChassisTable'");
		
			alSql.add("insert into data_state(neId,tableName,lastTime) values("
					+ sneid + ",'CM_tmnxChassisTable','" + sdate + "')");

			ClsDataBase.execSql(conn, alSql);
		}
	}

	// 风扇
	private void cfgChassisFanTable(String sneid)
			throws Exception {

		// 获取时间，格式如"2013-01-05 15:51:29"
		String sdate = String.format("%1$tY-%1$tm-%1$td %1$tH:%1$tM:%1$tS",
				new Date());
		ArrayList<String> alSql = new ArrayList<String>();
		String sql = "insert into CM_tmnxChassisFanTable(neId,tmnxChassisIndex,tmnxChassisFanIndex,"
				+ "tmnxChassisFanSpeed,tmnxChassisFanOperStatus,recTime) values("
				+ sneid + ",";

		ArrayList<HashMap<String, Variable>> rows;

		rows = snmpV2.getTable(MibConst.tmnxChassisFanTable);
		for (HashMap<String, Variable> row : rows) {
			alSql.add(sql
					+ row.get("instance").toString().replace('.', ',')
					+ ",'"
					+ MibConst.ChassisFanSpeed(row.get("tmnxChassisFanSpeed")
							.toInt())
					+ "','"
					+ MibConst.TmnxDeviceState(row.get(
							"tmnxChassisFanOperStatus").toInt()) + "','"
					+ sdate + "')");
		}

		// if(alSql.size()>0)
		// alSql.add("insert into data_state(neName,tableName,lastTime) values('"
		// + nename + "','CM_tmnxChassisFanTable','" + sdate +"')");

		//ClsDataBase.execSql(conn, alSql);

		if (alSql.size() > 0) {
			alSql.add("delete from data_state where neId="
					+ sneid + " and tableName='CM_tmnxChassisFanTable'");
			
			alSql.add("insert into data_state(neId,tableName,lastTime) values("
				+ sneid + ",'CM_tmnxChassisFanTable','" + sdate + "')");

			ClsDataBase.execSql(conn, alSql);
		}

	}

	// 电源
	private void cfgChassisPowerSupplyTable(String sneid)
			throws Exception {

		// 获取时间，格式如"2013-01-05 15:51:29"
		String sdate = String.format("%1$tY-%1$tm-%1$td %1$tH:%1$tM:%1$tS",
				new Date());
		ArrayList<String> alSql = new ArrayList<String>();
		String sql = "insert into CM_tmnxChassisPowerSupplyTable(neId,tmnxChassisIndex,tmnxChassisPowerSupplyId,"
				+ "tmnxChassisPowerSupply1Status,tmnxChassisPowerSupply2Status,tmnxChassisPowerSupplyAssignedType,"
				+ "tmnxChassisPowerSupplyACStatus,tmnxChassisPowerSupplyDCStatus,recTime) values("
				+ sneid + ",";

		ArrayList<HashMap<String, Variable>> rows;

		rows = snmpV2.getTable(MibConst.tmnxChassisPowerSupplyTable);
		for (HashMap<String, Variable> row : rows) {
			alSql.add(sql
					+ row.get("instance").toString().replace('.', ',')
					+ ",'"
					+ MibConst.TmnxDeviceState(row.get(
							"tmnxChassisPowerSupply1Status").toInt())
					+ "','"
					+ MibConst.TmnxDeviceState(row.get(
							"tmnxChassisPowerSupply2Status").toInt())
					+ "','"
					+ MibConst.TmnxChassisPowerSupplyAssignedType(row.get(
							"tmnxChassisPowerSupplyAssignedType").toInt())
					+ "','"
					+ MibConst.TmnxDeviceState(row.get(
							"tmnxChassisPowerSupplyACStatus").toInt())
					+ "','"
					+ MibConst.TmnxDeviceState(row.get(
							"tmnxChassisPowerSupplyDCStatus").toInt()) + "','"
					+ sdate + "')");
		}

		// if(alSql.size()>0)
		// alSql.add("insert into data_state(neName,tableName,lastTime) values('"
		// + nename + "','CM_tmnxChassisPowerSupplyTable','" + sdate +"')");

		//ClsDataBase.execSql(conn, alSql);

		if (alSql.size() > 0) {
			alSql.add("delete from data_state where neId="
					+ sneid + " and tableName='CM_tmnxChassisPowerSupplyTable'");
			
			alSql.add("insert into data_state(neId,tableName,lastTime) values("
					+ sneid + ",'CM_tmnxChassisPowerSupplyTable','" + sdate
					+ "')");

			ClsDataBase.execSql(conn, alSql);
		}

	}

	// 主控卡槽
	private void cfgCpmCardTable(String sneid) throws Exception {

		// 获取时间，格式如"2013-01-05 15:51:29"
		String sdate = String.format("%1$tY-%1$tm-%1$td %1$tH:%1$tM:%1$tS",
				new Date());
		ArrayList<String> alSql = new ArrayList<String>();
		String sql = "insert into CM_tmnxCpmCardTable(neId,tmnxChassisIndex,tmnxCpmCardSlotNum,tmnxCpmCardNum,"
				+ "tmnxCpmCardAssignedType,tmnxCpmCardEquippedType,tmnxCpmCardNumCpus,tmnxCpmCardCpuType,"
				+ "tmnxCpmCardMemorySize,tmnxCpmCardHwIndex,recTime) values("
				+ sneid + ",";

		ArrayList<HashMap<String, Variable>> rows;

		// 取出tmnxCardTypeTable
		HashMap<Integer, Variable> hmCardTypeTable = new HashMap<Integer, Variable>();
		rows = snmpV2.getTable(MibConst.tmnxCardTypeTable);
		for (HashMap<String, Variable> row : rows) {
			OID ins = (OID) row.get("instance");
			hmCardTypeTable.put(ins.getValue()[0],
					row.get("tmnxCardTypeDescription"));
		}

		rows = snmpV2.getTable(MibConst.tmnxCpmCardTable);
		for (HashMap<String, Variable> row : rows) {
			String tmnxCpmCardAssignedType = "", tmnxCpmCardEquippedType = "";

			if (hmCardTypeTable.containsKey(row.get("tmnxCpmCardAssignedType")
					.toInt()))
				tmnxCpmCardAssignedType = hmCardTypeTable.get(
						row.get("tmnxCpmCardAssignedType").toInt()).toString();
			else
				tmnxCpmCardAssignedType = "Unkown("
						+ row.get("tmnxCpmCardAssignedType").toString() + ")";

			if (hmCardTypeTable.containsKey(row.get("tmnxCpmCardEquippedType")
					.toInt()))
				tmnxCpmCardEquippedType = hmCardTypeTable.get(
						row.get("tmnxCpmCardEquippedType").toInt()).toString();
			else
				tmnxCpmCardEquippedType = "Unkown("
						+ row.get("tmnxCpmCardEquippedType").toString() + ")";

			alSql.add(sql
					+ row.get("instance").toString().replace('.', ',')
					+ ",'"
					+ tmnxCpmCardAssignedType
					+ "','"
					+ tmnxCpmCardEquippedType
					+ "',"
					+ row.get("tmnxCpmCardNumCpus").toString()
					+ ",'"
					+ MibConst.TmnxCpmCardCpuType(row.get("tmnxCpmCardCpuType")
							.toInt()) + "',"
					+ row.get("tmnxCpmCardMemorySize").toString() + ","
					+ row.get("tmnxCpmCardHwIndex").toString() + ",'" + sdate
					+ "')");
		}

		// if(alSql.size()>0)
		// alSql.add("insert into data_state(neName,tableName,lastTime) values('"
		// + nename + "','CM_tmnxCpmCardTable','" + sdate +"')");

		//ClsDataBase.execSql(conn, alSql);

		if (alSql.size() > 0) {
			alSql.add("delete from data_state where neId="
					+ sneid + " and tableName='CM_tmnxCpmCardTable'");
			
			alSql.add("insert into data_state(neId,tableName,lastTime) values("
					+ sneid + ",'CM_tmnxCpmCardTable','" + sdate + "')");

			ClsDataBase.execSql(conn, alSql);
		}

	}

	// 闪存
	private void cfgCpmFlashTable(String sneid) throws Exception {

		// 获取时间，格式如"2013-01-05 15:51:29"
		String sdate = String.format("%1$tY-%1$tm-%1$td %1$tH:%1$tM:%1$tS",
				new Date());
		ArrayList<String> alSql = new ArrayList<String>();
		String sql = "insert into CM_tmnxCpmFlashTable(neId,tmnxChassisIndex,tmnxCardSlotNum ,tmnxCpmFlashId,"
				+ "tmnxCpmFlashOperStatus,tmnxCpmFlashSerialNumber,tmnxCpmFlashFirmwareRevision,tmnxCpmFlashModelNumber,"
				+ "tmnxCpmFlashCapacity,tmnxCpmFlashUsed,tmnxCpmFlashHwIndex,recTime) values("
				+ sneid + ",";

		ArrayList<HashMap<String, Variable>> rows;

		rows = snmpV2.getTable(MibConst.tmnxCpmFlashTable);
		for (HashMap<String, Variable> row : rows) {
			alSql.add(sql
					+ row.get("instance").toString().replace('.', ',')
					+ ",'"
					+ MibConst.TmnxDeviceState(row
							.get("tmnxCpmFlashOperStatus").toInt()) + "','"
					+ row.get("tmnxCpmFlashSerialNumber").toString() + "','"
					+ row.get("tmnxCpmFlashFirmwareRevision").toString()
					+ "','" + row.get("tmnxCpmFlashModelNumber").toString()
					+ "'," + row.get("tmnxCpmFlashCapacity").toString() + ","
					+ row.get("tmnxCpmFlashUsed").toString() + ","
					+ row.get("tmnxCpmFlashHwIndex").toString() + ",'" + sdate
					+ "')");
		}

		// if(alSql.size()>0)
		// alSql.add("insert into data_state(neName,tableName,lastTime) values('"
		// + nename + "','CM_tmnxCpmFlashTable','" + sdate +"')");

		//ClsDataBase.execSql(conn, alSql);

		if (alSql.size() > 0) {
			alSql.add("delete from data_state where neId="
					+ sneid + " and tableName='CM_tmnxCpmFlashTable'");
			
			alSql.add("insert into data_state(neId,tableName,lastTime) values("
					+ sneid + ",'CM_tmnxCpmFlashTable','" + sdate + "')");

			ClsDataBase.execSql(conn, alSql);
		}

	}

	// 板卡槽
	private void cfgCardTable(String sneid) throws Exception {

		// 获取时间，格式如"2013-01-05 15:51:29"
		String sdate = String.format("%1$tY-%1$tm-%1$td %1$tH:%1$tM:%1$tS",
				new Date());
		ArrayList<String> alSql = new ArrayList<String>();
		String sql = "insert into CM_tmnxCardTable(neId,tmnxChassisIndex,tmnxCardSlotNum,"
				+ "tmnxCardAssignedType,tmnxCardEquippedType,tmnxCardMemorySize,tmnxCardHwIndex,"
				+ "tmnxCardNamedPoolAdminMode,tmnxCardNamedPoolOperMode,recTime) values(" + sneid + ",";

		ArrayList<HashMap<String, Variable>> rows;

		// 取出tmnxCardTypeTable
		HashMap<Integer, Variable> hmCardTypeTable = new HashMap<Integer, Variable>();
		rows = snmpV2.getTable(MibConst.tmnxCardTypeTable);
		for (HashMap<String, Variable> row : rows) {
			OID ins = (OID) row.get("instance");
			hmCardTypeTable.put(ins.getValue()[0],
					row.get("tmnxCardTypeDescription"));
		}

		rows = snmpV2.getTable(MibConst.tmnxCardTable);
		for (HashMap<String, Variable> row : rows) {
			String tmnxCardAssignedType = "", tmnxCardEquippedType = "";
			if (hmCardTypeTable.containsKey(row.get("tmnxCardAssignedType")
					.toInt()))
				tmnxCardAssignedType = hmCardTypeTable.get(
						row.get("tmnxCardAssignedType").toInt()).toString();
			else
				tmnxCardAssignedType = "Unkown("
						+ row.get("tmnxCardAssignedType").toString() + ")";

			if (hmCardTypeTable.containsKey(row.get("tmnxCardEquippedType")
					.toInt()))
				tmnxCardEquippedType = hmCardTypeTable.get(
						row.get("tmnxCardEquippedType").toInt()).toString();
			else
				tmnxCardEquippedType = "Unkown("
						+ row.get("tmnxCardEquippedType").toString() + ")";

			alSql.add(sql + row.get("instance").toString().replace('.', ',')
					+ ",'" + tmnxCardAssignedType + "','"
					+ tmnxCardEquippedType + "',"
					+ row.get("tmnxCardMemorySize").toString() + ","
					+ row.get("tmnxCardHwIndex").toString() + ","
					+ row.get("tmnxCardNamedPoolAdminMode").toString() + ","
					+ row.get("tmnxCardNamedPoolOperMode").toString() + ",'"
					+ sdate	+ "')");
		}

		// if(alSql.size()>0)
		// alSql.add("insert into data_state(neName,tableName,lastTime) values('"
		// + nename + "','CM_tmnxCardTable','" + sdate +"')");

		//ClsDataBase.execSql(conn, alSql);

		if (alSql.size() > 0) {
			alSql.add("delete from data_state where neId="
					+ sneid + " and tableName='CM_tmnxCardTable'");
			
			alSql.add("insert into data_state(neId,tableName,lastTime) values("
					+ sneid + ",'CM_tmnxCardTable','" + sdate + "')");

			ClsDataBase.execSql(conn, alSql);
		}

	}

	// 子板卡槽
	private void cfgMDATable(String sneid) throws Exception {

		// 获取时间，格式如"2013-01-05 15:51:29"
		String sdate = String.format("%1$tY-%1$tm-%1$td %1$tH:%1$tM:%1$tS",
				new Date());
		ArrayList<String> alSql = new ArrayList<String>();
		String sql = "insert into CM_tmnxMDATable(neId,tmnxChassisIndex,tmnxCardSlotNum,tmnxMDASlotNum,"
				+ "tmnxMDAAllowedTypes,tmnxMDAAssignedType,tmnxMDAEquippedType,tmnxMDAMaxPorts,"
				+ "tmnxMDAEquippedPorts,tmnxMDATxTimingSelected,tmnxMDASyncIfTimingStatus,"
				+ "tmnxMDANetworkIngQueues,tmnxMDACapabilities,tmnxMDAMinChannelization,"
				+ "tmnxMDAMaxChannelization,tmnxMDAHwIndex,recTime) values("
				+ sneid + ",";

		ArrayList<HashMap<String, Variable>> rows;

		// 取出tmnxMdaTypeTable
		HashMap<Integer, Variable> hmMdaTypeTable = new HashMap<Integer, Variable>();
		rows = snmpV2.getTable(MibConst.tmnxMdaTypeTable);
		for (HashMap<String, Variable> row : rows) {
			OID ins = (OID) row.get("instance");
			hmMdaTypeTable.put(ins.getValue()[0],
					row.get("tmnxMdaTypeDescription"));
		}

		rows = snmpV2.getTable(MibConst.tmnxMDATable);
		for (HashMap<String, Variable> row : rows) {
			String tmnxMDAAssignedType = "", tmnxMDAEquippedType = "";
			if (hmMdaTypeTable.containsKey(row.get("tmnxMDAAssignedType")
					.toInt()))
				tmnxMDAAssignedType = hmMdaTypeTable.get(
						row.get("tmnxMDAAssignedType").toInt()).toString();
			else
				tmnxMDAAssignedType = "Unkown("
						+ row.get("tmnxMDAAssignedType").toString() + ")";

			if (hmMdaTypeTable.containsKey(row.get("tmnxMDAEquippedType")
					.toInt()))
				tmnxMDAEquippedType = hmMdaTypeTable.get(
						row.get("tmnxMDAEquippedType").toInt()).toString();
			else
				tmnxMDAEquippedType = "Unkown("
						+ row.get("tmnxMDAEquippedType").toString() + ")";

			alSql.add(sql
					+ row.get("instance").toString().replace('.', ',')
					+ ",'"
					// +
					// hmMdaTypeTable.get(row.get("tmnxMDAAllowedTypes").toInt()).toString()
					// + "','"
					+ "','"
					+ tmnxMDAAssignedType
					+ "','"
					+ tmnxMDAEquippedType
					+ "',"
					+ row.get("tmnxMDAMaxPorts").toString()
					+ ","
					+ row.get("tmnxMDAEquippedPorts").toString()
					+ ",'"
					+ MibConst.TmnxDeviceState(row.get(
							"tmnxMDATxTimingSelected").toInt())
					+ "','"
					+ MibConst.TmnxMDASyncIfTimingStatus(row.get(
							"tmnxMDASyncIfTimingStatus").toInt())
					+ "','"
					+ row.get("tmnxMDANetworkIngQueues").toString()
					+ "','"
					+ MibConst.getMDACapabilities(row
							.get("tmnxMDACapabilities").toString())
					+ "','"
					+ MibConst.TmnxMDAChanType(row.get(
							"tmnxMDAMinChannelization").toInt())
					+ "','"
					+ MibConst.TmnxMDAChanType(row.get(
							"tmnxMDAMaxChannelization").toInt()) + "',"
					+ row.get("tmnxMDAHwIndex").toString() + ",'" + sdate
					+ "')");
		}

		//ClsDataBase.execSql(conn, alSql);

		// if(alSql.size()>0)
		// alSql.add("insert into data_state(neName,tableName,lastTime) values('"
		// + nename + "','CM_tmnxMDATable','" + sdate +"')");
		if (alSql.size() > 0) {
			alSql.add("delete from data_state where neId="
					+ sneid + " and tableName='CM_tmnxMDATable'");
			
			alSql.add("insert into data_state(neId,tableName,lastTime) values("
					+ sneid + ",'CM_tmnxMDATable','" + sdate + "')");

			ClsDataBase.execSql(conn, alSql);
		}

	}

	// 端口
	private void cfgPortTable(String sneid) throws Exception {

		// 获取时间，格式如"2013-01-05 15:51:29"
		String sdate = String.format("%1$tY-%1$tm-%1$td %1$tH:%1$tM:%1$tS",
				new Date());
		ArrayList<String> alSql = new ArrayList<String>();
		String sql = "insert into CM_tmnxPortTable(neId,tmnxChassisIndex,tmnxPortPortID,"
				+ "tmnxCardSlotNum,tmnxMDASlotNum,category,tmnxPortType,tmnxPortClass,"
				+ "tmnxPortName,tmnxPortMacAddress,tmnxPortHwMacAddress,tmnxPortMode,"
				+ "tmnxPortAdminStatus,tmnxPortOperStatus,tmnxPortState,tmnxPortEncapType,"
				+ "recTime) values(" + sneid + ",";

		ArrayList<HashMap<String, Variable>> rows;

		// 取出tmnxPortTypeTable
		HashMap<Integer, Variable> hmPortTypeTable = new HashMap<Integer, Variable>();
		rows = snmpV2.getTable(MibConst.tmnxPortTypeTable);
		for (HashMap<String, Variable> row : rows) {
			OID ins = (OID) row.get("instance");
			hmPortTypeTable.put(ins.getValue()[0],
					row.get("tmnxPortTypeDescription"));
		}

		rows = snmpV2.getTable(MibConst.tmnxPortTable);
		for (HashMap<String, Variable> row : rows) {
			OID ins = (OID) row.get("instance");
			String tmnxPortType = "";
			if (hmPortTypeTable.containsKey(row.get("tmnxPortType").toInt()))
				tmnxPortType = hmPortTypeTable.get(
						row.get("tmnxPortType").toInt()).toString();
			else
				tmnxPortType = "Unkown(" + row.get("tmnxPortType").toString()
						+ ")";

			alSql.add(sql
					+ row.get("instance").toString().replace('.', ',')
					+ ","
					+ MibConst.getPortAttrib(ins.getValue()[1])
					+ ",'"
					+ tmnxPortType
					+ "','"
					+ MibConst.TmnxPortClass(row.get("tmnxPortClass").toInt())
					+ "','"
					+ row.get("tmnxPortName").toString()
					+ "','"
					+ row.get("tmnxPortMacAddress").toString()
					+ "','"
					+ row.get("tmnxPortHwMacAddress").toString()
					+ "','"
					+ MibConst.TmnxPortMode(row.get("tmnxPortMode").toInt())
					+ "','"
					+ MibConst.TmnxPortAdminStatus(row.get(
							"tmnxPortAdminStatus").toInt())
					+ "','"
					+ MibConst.TmnxPortOperStatus(row.get("tmnxPortOperStatus")
							.toInt()) + "','"
					+ MibConst.TmnxPortState(row.get("tmnxPortState").toInt())
					+ "'," + row.get("tmnxPortEncapType").toString()
					+ ",'" + sdate + "')");
		}

		// if(alSql.size()>0)
		// alSql.add("insert into data_state(neName,tableName,lastTime) values('"
		// + nename + "','CM_tmnxPortTable','" + sdate +"')");

		//ClsDataBase.execSql(conn, alSql);

		if (alSql.size() > 0) {
			alSql.add("delete from data_state where neId="
					+ sneid + " and tableName='CM_tmnxPortTable'");
			
			alSql.add("insert into data_state(neId,tableName,lastTime) values("
					+ sneid + ",'CM_tmnxPortTable','" + sdate + "')");

			ClsDataBase.execSql(conn, alSql);
		}

	}

	// 硬件信息表
	private void cfgHwTable(String sneid) throws Exception {

		// 获取时间，格式如"2013-01-05 15:51:29"
		String sdate = String.format("%1$tY-%1$tm-%1$td %1$tH:%1$tM:%1$tS",
				new Date());
		ArrayList<String> alSql = new ArrayList<String>();
		String sql = "insert into CM_tmnxHwTable(neId,tmnxChassisIndex,tmnxHwIndex,"
				+ "tmnxHwClass,tmnxHwName,tmnxHwBaseMacAddress,tmnxHwAdminState,"
				+ "tmnxHwOperState,recTime) values(" + sneid + ",";

		ArrayList<HashMap<String, Variable>> rows;

		rows = snmpV2.getTable(MibConst.tmnxHwTable);
		for (HashMap<String, Variable> row : rows) {
			alSql.add(sql
					+ row.get("instance").toString().replace('.', ',')
					+ ",'"
					+ MibConst.TmnxHwClass(row.get("tmnxHwClass").toInt())
					+ "','"
					+ row.get("tmnxHwName").toString()
					+ "','"
					+ row.get("tmnxHwBaseMacAddress").toString()
					+ "','"
					+ MibConst.TmnxHwAdminState(row.get("tmnxHwAdminState")
							.toInt())
					+ "','"
					+ MibConst.TmnxHwOperState(row.get("tmnxHwOperState")
							.toInt()) + "','" + sdate + "')");
		}

		// if(alSql.size()>0)
		// alSql.add("insert into data_state(neName,tableName,lastTime) values('"
		// + nename + "','CM_tmnxHwTable','" + sdate +"')");

		//ClsDataBase.execSql(conn, alSql);

		if (alSql.size() > 0) {
			alSql.add("delete from data_state where neId="
					+ sneid + " and tableName='CM_tmnxHwTable'");
			
			alSql.add("insert into data_state(neId,tableName,lastTime) values("
					+ sneid + ",'CM_tmnxHwTable','" + sdate + "')");

			ClsDataBase.execSql(conn, alSql);
		}
	}

	// //////////////////////////////////////////////////////////////////////////

	@Override
	public PDU get(int iNeId, String oid) throws Exception {
		return get(iNeId, new OID(oid));
	}

	@Override
	public PDU get(int iNeId, OID oid) throws Exception {
		@SuppressWarnings("rawtypes")
		HashMap ne = getNe(iNeId);
		if (null == ne)
			return null;

		String ipaddr = (String) ne.get("ipaddr");
		String snmpcommunityread = (String) ne.get("snmpcommunityread");
		if (snmpcommunityread.isEmpty())
			snmpcommunityread = getCommunity;
		String nename = (String) ne.get("nename");

		SnmpV2 snmpv2 = new SnmpV2(ipaddr, snmpcommunityread, snmpRetries,
				snmpTimeOut);

		PDU pdu = snmpv2.get(oid);
		snmpv2.close();

		log.info("get " + oid.toString() + " from " + nename + "(" + ipaddr
				+ ")");

		return pdu;
	}

	@Override
	public PDU get(int iNeId, VariableBinding[] vbs) throws Exception {
		@SuppressWarnings("rawtypes")
		HashMap ne = getNe(iNeId);
		if (null == ne)
			return null;

		String ipaddr = (String) ne.get("ipaddr");
		String snmpcommunityread = (String) ne.get("snmpcommunityread");
		if (snmpcommunityread.isEmpty())
			snmpcommunityread = getCommunity;
		String nename = (String) ne.get("nename");

		SnmpV2 snmpv2 = new SnmpV2(ipaddr, snmpcommunityread, snmpRetries,
				snmpTimeOut);

		PDU pdu = snmpv2.get(vbs);
		snmpv2.close();

		log.info("get vbs from " + nename + "(" + ipaddr + ")");

		return pdu;
	}

	@Override
	public PDU getNext(int iNeId, String preOid) throws Exception {
		return getNext(iNeId, new OID(preOid));
	}

	@Override
	public PDU getNext(int iNeId, OID preOid) throws Exception {
		@SuppressWarnings("rawtypes")
		HashMap ne = getNe(iNeId);
		if (null == ne)
			return null;

		String ipaddr = (String) ne.get("ipaddr");
		String snmpcommunityread = (String) ne.get("snmpcommunityread");
		if (snmpcommunityread.isEmpty())
			snmpcommunityread = getCommunity;
		String nename = (String) ne.get("nename");

		SnmpV2 snmpv2 = new SnmpV2(ipaddr, snmpcommunityread, snmpRetries,
				snmpTimeOut);

		PDU pdu = snmpv2.getNext(preOid);
		snmpv2.close();

		log.info("get next " + preOid.toString() + " from " + nename + "("
				+ ipaddr + ")");

		return pdu;
	}

	@Override
	public ArrayList<PDU> walk(int iNeId, String baseOid) throws Exception {
		return walk(iNeId, new OID(baseOid));
	}

	@Override
	public ArrayList<PDU> walk(int iNeId, OID baseOid) throws Exception {
		@SuppressWarnings("rawtypes")
		HashMap ne = getNe(iNeId);
		if (null == ne)
			return null;

		String ipaddr = (String) ne.get("ipaddr");
		String snmpcommunityread = (String) ne.get("snmpcommunityread");
		if (snmpcommunityread.isEmpty())
			snmpcommunityread = getCommunity;
		String nename = (String) ne.get("nename");

		SnmpV2 snmpv2 = new SnmpV2(ipaddr, snmpcommunityread, snmpRetries,
				snmpTimeOut);

		ArrayList<PDU> arPdu = snmpv2.walk(baseOid);
		snmpv2.close();

		log.info("walk on " + baseOid.toString() + " from " + nename + "("
				+ ipaddr + ")");

		return arPdu;
	}

	@Override
	public PDU set(int iNeId, VariableBinding vb) throws Exception {
		@SuppressWarnings("rawtypes")
		HashMap ne = getNe(iNeId);
		if (null == ne)
			return null;

		String ipaddr = (String) ne.get("ipaddr");
		String snmpcommunitywrite = (String) ne.get("snmpcommunitywrite");
		if (snmpcommunitywrite.isEmpty())
			snmpcommunitywrite = setCommunity;
		String nename = (String) ne.get("nename");

		SnmpV2 snmpv2 = new SnmpV2(ipaddr, snmpcommunitywrite, snmpRetries,
				snmpTimeOut);

		PDU pdu = snmpv2.set(vb);
		snmpv2.close();

		log.info("set " + vb.toString() + " to " + nename + "(" + ipaddr + ")");

		return pdu;
	}

	@Override
	public PDU set(int iNeId, VariableBinding[] vbs) throws Exception {
		@SuppressWarnings("rawtypes")
		HashMap ne = getNe(iNeId);
		if (null == ne)
			return null;

		String ipaddr = (String) ne.get("ipaddr");
		String snmpcommunitywrite = (String) ne.get("snmpcommunitywrite");
		if (snmpcommunitywrite.isEmpty())
			snmpcommunitywrite = setCommunity;
		String nename = (String) ne.get("nename");

		SnmpV2 snmpv2 = new SnmpV2(ipaddr, snmpcommunitywrite, snmpRetries,
				snmpTimeOut);

		PDU pdu = snmpv2.set(vbs);
		snmpv2.close();

		log.info("set vbs to " + nename + "(" + ipaddr + ")");

		return pdu;
	}

	@Override
	public ArrayList<HashMap<String, Variable>> getTable(int iNeId,
			HashMap<String, OID> entry) throws Exception {

		@SuppressWarnings("rawtypes")
		HashMap ne = getNe(iNeId);
		if (null == ne)
			return null;

		String ipaddr = (String) ne.get("ipaddr");
		String snmpcommunityread = (String) ne.get("snmpcommunityread");
		if (snmpcommunityread.isEmpty())
			snmpcommunityread = getCommunity;
		String nename = (String) ne.get("nename");

		SnmpV2 snmpv2 = new SnmpV2(ipaddr, snmpcommunityread, snmpRetries,
				snmpTimeOut);

		ArrayList<HashMap<String, Variable>> rows = snmpv2.getTable(entry);
		snmpv2.close();

		log.info("get table (fields: '" + entry.keySet().iterator().next()
				+ "' ...) from " + nename + "(" + ipaddr + ")");

		return rows;
	}

}
